package com.ht.dao;

import com.ht.bean.Aduitlog;
import com.ht.bean.Emp;
import com.ht.bean.HolidayStudent;
import com.ht.bean.Studentfloor;
import com.ht.bean.Studenthuor;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

public interface XtbbDao {




/////员工考核报表////////////////////////////////////////////////////////////////////////////////////////////////////
    //员工考核id查询 模糊查询
    @Select({"<script>",
            "select al.aduitLogId,a.aduitName,ep.empName,a.scores,al.auditDate,al.remark,al.auditPerson,al.image from aduitlog al , emp ep , aduitmodel a ",
            "<where>",
            " al.aduitModelId=a.aduitModelId and al.empId=ep.empId",
            "<if test=\"al.aduitLogId!=null and al.aduitLogId!=''\">",
            " and al.aduitLogId=#{al.aduitLogId}",
            "</if>",
            "<if test=\"ep.empId!=null and ep.empId!=''\">",
            " and ep.empId=#{ep.empId}",
            "</if>",
            "</where>",
            " limit #{startIndex},#{pageSize}",
            "</script>"})
    List<Map> ygkhlist(@Param("startIndex") Integer startIndex, @Param("pageSize")Integer pageSize, @Param("al") Aduitlog aduitlog, @Param("ep") Emp emp);

    //员工考核id查询 模糊查询的总页数
    @Select({"<script>",
            "select count(*) from aduitlog al , emp ep , aduitmodel a ",
            "<where>",
            " al.aduitModelId=a.aduitModelId and al.empId=ep.empId",
            "<if test=\"al.aduitLogId!=null and al.aduitLogId!=''\">",
            " and al.aduitLogId=#{al.aduitLogId}",
            "</if>",
            "<if test=\"ep.empId!=null and ep.empId!=''\">",
            " and ep.empId=#{ep.empId}",
            "</if>",
            "</where>",
            "</script>"})
    public int ygkhcount(@Param("al") Aduitlog aduitlog, @Param("ep") Emp emp);



    //查询员工表
    @Select("select * from emp")
    List<Emp>ygkhemp();


///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////









    //宿舍报表
    //宿舍
    @Select("select s.huorId ,s.huorName,s. from studenthuor s ")
    List<Map> lisshushe();

    //楼栋
    @Select("select s.floorId ,s.floorName from studentfloor s ")
    List<Map> lislou();

    //id查询 模糊查询
    @Select({"<script>",
            "select st.huorId,st.huorName,st.addr,st.floorId,st.count,st.numberBeds,(st.numberBeds - st.count ) kon from studenthuor st",
            "<where>",
            "<if test=\"st.huorName!=null and st.huorName!=''\">",
            " and st.huorName like concat('%',#{st.huorName},'%')",
            "</if>",
            "<if test=\"st.floorId!=null and st.floorId!=''\">",
            " and st.floorId = #{st.floorId}",
            "</if>",
            "</where>",
            " limit #{pid},#{lime}",
            "</script>"})
    List<Map> sushelist(@Param("pid") Integer pid, @Param("lime") Integer lime ,@Param("st") Studenthuor studenthuor);

    //id查询 模糊查询
    @Select({"<script>",
            "select ss.floorName,sum(s.count) ren,sum(s.numberBeds-s.count) kon from studenthuor s ,studentfloor ss where s.floorId = ss.floorId group by s.floorId having count(s.floorId)>0",
            "</script>"})
    List<Map> sushelistt();


    //id查询 模糊查询的总页数
    @Select({"<script>",
            "select count(*) from studenthuor st",
            "<where>",
            "<if test=\"st.huorName!=null and st.huorName!=''\">",
            " and st.huorName like concat('%',#{st.huorName},'%')",
            "</if>",
            "<if test=\"st.floorId!=null and st.floorId!=''\">",
            " and st.floorId = #{st.floorId}",
            "</if>",
            "</where>",
            "</script>"})
    public int sushecount(@Param("st") Studenthuor studenthuor);

    //id查询宿舍表总人数
    @Select({"<script>",
            "select sum(st.numberBeds) from studenthuor st",
            "<where>",
            "<if test=\"st.huorName!=null and st.huorName!=''\">",
            " and st.huorName like concat('%',#{st.huorName},'%')",
            "</if>",
            "<if test=\"st.floorId!=null and st.floorId!=''\">",
            " and st.huorName = #{st.floorId}",
            "</if>",
            "</where>",
            "</script>"})
    int sumhecount(@Param("st") Studenthuor studenthuor);

    @Select({"<script>",
            "select s.stuId , s.stuname,count(h.status) from holidaystudent h,student s ",
            "<where>",
            "and h.stuId = s.stuId",
            "<if test=\"emp.empName!=null and emp.empName!=''\">",
            " and emp.empName like concat('%',#{emp.empName},'%')",
            "</if>",
            "<if test=\"emp.depId!=null and emp.depId!=''\">",
            " and emp.depId = #{emp.depId}",
            "</if>",
            "<if test=\"emp.depId!=null and emp.depId!=''\">",
            " and emp.depId = #{emp.depId}",
            "</if>",
            "<if test=\"emp.depId!=null and emp.depId!=''\">",
            " and DATE_FORMAT(h.startTime,'%Y-%m') = #{}",
            "</if>",
            "</where>",
            " limit #{pid},#{lime}",
            "</script>"})
    List<Map> lisjia(@Param("pid") Integer pid, @Param("lime") Integer lime , @Param("emp")Emp emp);

 @Select({"<script>",
            "select h.empId,e.empName,d.depName,count(*) shu from emp e ,holiday h ,dep d ",
            "<where>",
            "and e.empId = h.empId and e.depId = d.depid",
            "<if test=\"emp.empName!=null and emp.empName!=''\">",
            " and e.empName like concat('%',#{emp.empName},'%')",
            "</if>",
            "<if test=\"emp.depId!=null and emp.depId!=''\">",
            " and d.depid = #{emp.depId}",
            "</if>",
            "<if test=\"yue==2 and yue!=''\">",
            " and date_format(h.startTime, '%Y %m') = date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y %m')",
            "</if>",
             "<if test=\"yue==3 and yue!=''\">",
             " and date_format(h.startTime, '%Y %m') = date_format(DATE_SUB(curdate(), INTERVAL 0 MONTH),'%Y %m')",
             "</if>",
            "</where>",
            " group by h.empId having count(e.empId)>0 limit #{pid},#{lime}",
            "</script>"})
    List<Map> lisyuan(@Param("pid") Integer pid, @Param("lime") Integer lime , @Param("emp")Emp emp,@Param("yue") String yue);

    @Select({"<script>",
            "select count(*) from (select h.empId,e.empName,d.depName,count(*) shu from emp e ,holiday h ,dep d ",
            "<where>",
            "and e.empId = h.empId and e.depId = d.depid",
            "<if test=\"emp.empName!=null and emp.empName!=''\">",
            " and e.empName like concat('%',#{emp.empName},'%')",
            "</if>",
            "<if test=\"emp.depId!=null and emp.depId!=''\">",
            " and d.depid = #{emp.depId}",
            "</if>",
            "<if test=\"yue==2 and yue!=''\">",
            " and date_format(h.startTime, '%Y %m') = date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y %m')",
            "</if>",
            "<if test=\"yue==3 and yue!=''\">",
            " and date_format(h.startTime, '%Y %m') = date_format(DATE_SUB(curdate(), INTERVAL 0 MONTH),'%Y %m')",
            "</if>",
            "</where>",
            " group by h.empId having count(e.empId)>0 )as T_BL_symptom_new2020",
            "</script>"})
    public Integer lisyuann( @Param("emp")Emp emp,@Param("yue") String yue);

    @Select("select d.depName,count(*) shu from emp e ,holiday h ,dep d where e.empId = h.empId and e.depId = d.depid group by d.depid having count(e.empId)>0")
     List<Map> lisyuannn();



}
